﻿<cfsilent>
	<cfscript>

		temFile = GetTempDirectory() & createUUID() & ".xls";
		
		termId = event.getArg("TermID", event.getArg("TID"));
		
		termstart = event.getArg("DateStart");
		
		sql = "SELECT a.academic_year,
					   a.academic_term 
				FROM t_term a 
				WHERE a.term_id = :termId ";
		
		queryObj = new Query( datasource=application.dnsSlave ) ;
		queryObj.addParam( name="termId", value=termId, cfsqltype="cf_sql_char" );
		
		rs_term= queryObj.execute( sql=sql ).getResult();

		downFile = URLEncodedFormat( rs_term.academic_year & "-" & rs_term.academic_term & "补考课程安排", "utf-8");

		/* 生成电子表格数据 */
					
		sql = "SELECT 
				d.institute_name, 
				c.course_name, 
				c.course_credit, 
				case when c.course_code is null then '-' else c.course_code end course_code, 
				b.task_exrstid, 
				b.wid, 
				b.wdidx, 
				e.cellidx, 
				e.time_start, 
				e.time_end, 
				f.classroom, 
				f.cr_floor, 
				g.b_name, 
				count(*) count1
				FROM t_student_resit a  
					 INNER JOIN t_resittask b ON b.task_exrstid = a.task_exrstid 
					 INNER JOIN t_course c ON c.cid = b.cid 
					 INNER JOIN t_institute d ON d.institute_id = c.institute_id 
					 INNER JOIN t_examcell e ON e.cellidx = b.cellidx 
					 INNER JOIN t_Classroom f ON f.cr_id = a.cr_id 
					 INNER JOIN t_building g ON g.b_id = f.b_id 
				WHERE 
					b.term_id = :termId 
					AND 
					b.task_prog = '3' 
				GROUP BY 
					d.institute_name, 
					c.course_name, 
					c.course_credit, 
					course_code, 
					b.task_exrstid, 
					b.wid, 
					b.wdidx, 
					e.cellidx, 
					e.time_start, 
					e.time_end, 
					f.classroom, 
					f.cr_floor, 
					g.b_name 
				ORDER BY 
					d.institute_name, 
					b.wid, 
					b.wdidx, 
					c.course_name ";
					
		queryObj = new Query( datasource=application.dnsSlave ) ;
		queryObj.addParam( name="termId", value=termId, cfsqltype="cf_sql_char" );
		
		rs_exam= queryObj.execute( sql=sql ).getResult();
		
		/* 载入相关操作组件 */
		excel = getProperty("serviceFactory").getBean("spreadSheetObject");
		mathAdvice = getProperty("serviceFactory").getBean("senateMathAdvice");
		datetimeAdvice = getProperty("serviceFactory").getBean("datetimeAdvice");
		dictAdvice = getProperty("serviceFactory").getBean("senateDictionaryAdvice");
		
		/* 创建工作簿 */
		spreadsheetObj = excel.SpreadSheetNew("补考课程及考场", false);
		
		/* 添加表头 */
		excel.SpreadsheetAddrow(spreadsheetObj, "任课单位,课程,学分,课程号,考试编号,考试时间,考试周次,场次安排,起止时间,考场,教学楼,所在楼层,参考人数");
		
		row = 1;
		
		/* 写入考场安排 */
		for (    a=1; a LTE rs_exam.recordCount; a++    ) {
			
			row++;
			
			rowData = rs_exam["institute_name"][a]
						& "," & rs_exam["course_name"][a] 
						& "," & numberFormat( rs_exam["course_credit"][a], "_.__" ) 
						& "," & rs_exam["course_code"][a] 
						& "," & rs_exam["task_exrstid"][a] 
						& "," & datetimeAdvice.formatDateString( mathAdvice.revertTaskWeekDay( termstart, rs_exam["wid"][a], rs_exam["wdidx"][a] ), "YYYY年MM月DD日") & " (" & datetimeAdvice.formatTimeString( rs_exam["time_start"][a], "HH:MI" ) & "~" & datetimeAdvice.formatTimeString( rs_exam["time_end"][a], "HH:MI" ) & ")"
						& "," & rs_exam["wid"][a] 
						& "," & mathAdvice.convertWeekDayToChinese( rs_exam["wdidx"][a] ) & mathAdvice.convertExamIdxToChinese( rs_exam["cellidx"][a] ) 
						& "," & datetimeAdvice.formatTimeString( rs_exam["time_start"][a], "HH:MI" ) & "~" & datetimeAdvice.formatTimeString( rs_exam["time_end"][a], "HH:MI" )
						& "," & rs_exam["classroom"][a] 
						& "," & rs_exam["b_name"][a] 
						& "," & dictAdvice.getFloorString( rs_exam["cr_floor"][a] ) 
						& "," & rs_exam["count1"][a];
			
			excel.SpreadsheetAddrow(spreadsheetObj, rowData); 
			
		}
	
		excel.SpreadSheetWrite(spreadsheetObj, temFile, true);
	
	</cfscript>
	<cfheader name="Content-Disposition" value="attachment; filename=#downFile#.xls" />
	<cfcontent file="#temFile#" reset="yes" type="application/msexcel" deletefile="yes" />
</cfsilent>